CREATE PROCEDURE sp_iboGetCounter
  @CounterName varchar(30) = '' , @IncrementBy   int = 1 , @FirstUnique int OUTPUT
  /* version 10/7/03 */
  /***   sp_iboGetCounter  For a given CounterName, returns the next value. (Guaranteed unique).
      If you supply the optional second param "incrementBy", then you receive the additional guarantee that    
      you can use that many consecutive numbers, all with a uniqueness guarantee.  
  Sample use:  ----------------------------------------------------------------    
      exec sp_iboGetCounter  'MyCounterName', 3  
      returns     101  Now you know that 101, 102, 103 are all usable unique numbers   
  Revisions: ------------------------------------------------------------------- x/x/xx    
  Initial version 1/8/97   mw added optional second param IncrementBy  
  Revision 4/24/01   scd added Transaction and locking, output parm rather than recordset   
  Revision 10/7/2003 Change to update first, only insert if fails to remove contention and reduce deadlocks********/
  AS
  BEGIN TRANSACTION
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  declare @id varchar(10),@NewId varchar(10), @LastValue int,@checksum int,@strChecksum varchar(1),
  @digit int,@weight int,@subpos int,@sum int,@weights varchar(9),@pos int
  if @CounterName in ('Name','Prospect') and
  (select ShortValue from System_Params where ParameterName='Member_Control.UseChecksumForId')='YES'
  BEGIN
   select @weights='2345672345'
   select @LastValue=LAST_VALUE from Counter where COUNTER_NAME=@CounterName
   if (select HAS_CHECKSUM from Counter where COUNTER_NAME=@CounterName)=1
       BEGIN
        select @id=convert(varchar(10),@LastValue)
        select @id=substring(@id,1,datalength(@id)-1)
        select @LastValue=convert(int,@id)+1
        select @id=convert(varchar(10),@LastValue)
       END
   ELSE
       BEGIN
        select @LastValue=@LastValue+1
        select @id=convert(varchar(10),@LastValue)
       END
       select @subpos=datalength(@id)+1
       select @pos=0
       while @pos<datalength(@id)
       BEGIN
        select @pos=@pos+1
        select @weight=convert(int,(substring(@weights,@pos,1)))
        select @digit=convert(int,(substring(@id,@subpos-@pos,1)))
        select @sum=isnull(@sum,0)+(@weight*@digit)
       END
       select @checksum=@sum%11
       if @checksum=0
       BEGIN
        select @checksum=1
       END
       select @checksum=(11-@checksum)%10
       select @strChecksum=convert(varchar(1),@checksum)
       select @NewId=@id+@strChecksum
        update Counter
        set LAST_VALUE=convert(int,@NewId) , LAST_UPDATED=getdate(), UPDATED_BY=user_name(),HAS_CHECKSUM=1,@FirstUnique = convert(int,@NewId)
        where COUNTER_NAME= @CounterName
        if @@ROWCOUNT = 0
        BEGIN
          insert into Counter(COUNTER_NAME, LAST_VALUE) select @CounterName,0
         where not exists (select * from Counter where COUNTER_NAME = @CounterName)
         update Counter
            set LAST_VALUE=convert(int,@NewId) , LAST_UPDATED=getdate(), UPDATED_BY=user_name(),HAS_CHECKSUM=1,@FirstUnique = convert(int,@NewId)
          where COUNTER_NAME= @CounterName   
        END
      END
  ELSE
  BEGIN
    update Counter         
       set LAST_VALUE=LAST_VALUE +@IncrementBy ,               
           LAST_UPDATED=getdate(),
           UPDATED_BY=user_name(),
         @FirstUnique = LAST_VALUE + 1                  
       where COUNTER_NAME= @CounterName
    if @@ROWCOUNT = 0
    BEGIN
      insert into Counter(COUNTER_NAME, LAST_VALUE) select @CounterName,0
     where not exists (select * from Counter where COUNTER_NAME = @CounterName)
     update Counter
       set LAST_VALUE=LAST_VALUE + @IncrementBy ,               
           LAST_UPDATED=getdate(),
           UPDATED_BY=user_name(),
         @FirstUnique = LAST_VALUE + 1                 
      where COUNTER_NAME= @CounterName   
    END    
  END  
   COMMIT TRANSACTION

GO